package com.wd.print;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

import com.wd.dao.AlexaDAOImpl;
import com.wd.model.AlexaDataReference;
import com.wd.util.DataUtil;

public class Excel {

	public static void createXls(String path, String name, String date,
			int type, List<AlexaDataReference> modelList) {
		/** Excel 文件要存放的位置，假定在工程目录下 */
		String title = "网站流量数据分析";
		String outputFile = path + name + date + title + ".xls";
		try {
			// create a new excel wookbook
			HSSFWorkbook workbook = new HSSFWorkbook();
			// create a new sheet,default name 0
			HSSFSheet sheet = workbook.createSheet(title);
			// create row ,row index start with 0
			String rowTitleStr = title;
			HSSFRow rowTitle = sheet.createRow(0);
			HSSFCell cellTitle = rowTitle.createCell(0);
			cellTitle.setCellValue(rowTitleStr);
			// set title cell style
			HSSFCellStyle cellTitleStyle = workbook.createCellStyle();
			// set title Font
			Font font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 18);
			font.setColor(Font.COLOR_RED);
			cellTitleStyle.setFont(font);
			cellTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellTitle.setCellStyle(cellTitleStyle);
			// merger CellRangeAddress(first row,last row,first column,last
			// column);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
			HSSFRow rowTh = sheet.createRow(1);
			HSSFCell cellTh = rowTh.createCell(0);
			HSSFCellStyle cellThStyle = workbook.createCellStyle();
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
			font.setFontName("宋体");
			cellThStyle.setFont(font);
			if (type == 1) {
				cellTh.setCellValue("证券行业名次");
			} else if (type == 2) {
				cellTh.setCellValue("期货行业名次");
			} else if (type == 3) {
				cellTh.setCellValue("基金行业名次");
			}
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(1);
			cellTh.setCellValue("基金名称");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(2);
			cellTh.setCellValue("全球综合排名");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(3);
			cellTh.setCellValue("中文网站排名");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(4);
			cellTh.setCellValue("排名变化趋势");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(5);
			cellTh.setCellValue("访问速度");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(6);
			cellTh.setCellValue("反向链接");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(7);
			cellTh.setCellValue("人均页面访问量");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(8);
			cellTh.setCellValue("来自搜索引擎%");
			cellTh.setCellStyle(cellThStyle);
			cellTh = rowTh.createCell(9);
			cellTh.setCellValue("用户分布范围");
			cellTh.setCellStyle(cellThStyle);
			for (int i = 0; i < rowTh.getLastCellNum(); i++) {
				String c = rowTh.getCell(i).getStringCellValue();
				if (c.length() >= 6) {
					sheet.setColumnWidth(i, 3500);
				} else {
					sheet.setColumnWidth(i, 2500);
				}
			}
			HSSFRow rowTd = null;
			HSSFCell cellTd = null;
			HSSFCellStyle cellTdStyle = workbook.createCellStyle();
			font = workbook.createFont();
			font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
			font.setFontName("宋体");
			cellTdStyle.setFont(font);
			// create new rows
			for (int i = 0; i < modelList.size(); i++) {
				rowTd = sheet.createRow(i + 2);
				AlexaDataReference model = modelList.get(i);
				cellTd = rowTd.createCell(0);
				cellTd.setCellValue(i + 1);
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(1);
				cellTd.setCellValue(model.getEnt_name());
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(2);
				cellTd.setCellValue(model.getTraffic_rank());
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(3);
				cellTd.setCellValue(model.getTraffic_rank_cn());
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(4);
				cellTd.setCellValue(model.getThree_m_growth_movement());
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(5);
				cellTd.setCellValue(DataUtil.getFormatData((double) model
						.getAvg_load_time(), 3));
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(6);
				cellTd.setCellValue(model.getSite_linking_in());
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(7);
				cellTd.setCellValue(DataUtil.getFormatData((double) model
						.getPageviews_per_user(), 1));
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(8);
				cellTd.setCellValue(DataUtil.getFormatData((double) model
						.getSe_referer_percentage(), 5));
				cellTd.setCellStyle(cellTdStyle);
				cellTd = rowTd.createCell(9);
				cellTd.setCellValue(getSubDomainPercent(model
						.getSub_domain_spread()));
				cellTd.setCellStyle(cellTdStyle);
			}
			FileOutputStream fOut = new FileOutputStream(outputFile);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
			System.out.println("文件生成...");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static String getSubDomainPercent(String subDomain) {
		StringBuffer subDomainStr = new StringBuffer();
		if (subDomain == null || "".equals(subDomain)) {
			return null;
		} else {
			String str[] = subDomain.split("#");
			if (str.length <= 1) {
				return null;
			} else if (str.length == 2) {
				String percent[] = str[0].split("\\|");
				String subDomains[] = str[1].split("\\|");
				for (int j = 0; j < percent.length; j++) {
					if (j > 0) {
						subDomainStr.append("|");
					}
					subDomainStr.append(percent[j] + "（" + subDomains[j] + "）");
				}
			}
		}
		return subDomainStr.toString();
	}

	public static void main(String[] args) {
		AlexaDAOImpl daoImpl = new AlexaDAOImpl();
		Excel.createXls("./", "汇添富", "20091208", 3, daoImpl
				.getAlexaDataReferences("20091208", 3));
	}
}
